import json
from datetime import datetime

import requests
from urllib.parse import urljoin

import pymysql

BASE_URL = 'http://cmp.msa.gov.cn/cbdj_mgt/dataquery/shipCertQuery.action?act=mortgage&certMortgageQO.org_type=10&certMortgageQO.org_code=140000&certMortgageQO.approval_date_startStr=2024-01-01&certMortgageQO.approval_date_endStr=2025-02-14&page=1&rows=10'
headers = {
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7',
    # Content-Type设置为application/json，接口才能返回json数据
    'Content-Type': 'application/json;charset=utf-8'
}
cookies = {
    # OAMAuthnCookie_cmp.msa.gov.cn:80 参数用于登录认证
    'OAMAuthnCookie_cmp.msa.gov.cn:80': 'nObqYnKXp%2F2GyjW19fcV8kx3kEbgNY8aQbzA47axyUC8kV1%2FXz5w2n8pXsEkoSolbn7%2FLNXsdFJPwXx3V5ftCch9XH6c5N3994V%2BnEig1ETj3v%2BTVLfYKG6VpS75NK%2F%2BTFG9Q2%2Fv7EoryVRb2gCVe3wLclYh%2Bx%2FC2qBoFmxLZwwfrB8Sb9jaeDyfi%2FISfZfG1rkIAgs%2F7CyfE2xL3%2FvT2g0Ydk044T9WQ0rCPLB66zY4J%2FNfReWT4UuegfqXigHNZm8hKoyEvSO2RzIHKNKVcOZbIQSOUa7W3VCKjLdJLPraqw2Fh5gTJo5I2VZBS1hhxbGvrMlH9tayCKlWl3S%2B28BkFyK8LZygsqRlIRVDYkrnhBv1HCG7xJjGhNOpH4My%2BZB4g9jjMFv9X%2BdUKcm9bJqbpI4%2BciEhm%2BOIwWYVSOTdU4r08%2FSBqGNF0ylvTdniDOHlV7e1hVSR66o%2B9dNAZHlGJy54xb%2F4u8p0kLEQUGg%3D',

    # OAMAuthnHintCookie和JSESSIONID 参数用于声明返回接口数据，不配置的会一直返回HTML
    'OAMAuthnHintCookie': '1',
    'JSESSIONID': 'q1sFB7jI5DabEVsDjAQxwWlX-t0qs-1YlAL6smkC7fkRxorPUvyy!-1812561480'
}

session = requests.Session()
response_lo = session.post(BASE_URL, headers=headers, cookies=cookies)
print(response_lo.text)
strData = json.loads(response_lo.text)


if response_lo.status_code == 200:
    # 获取接口数据
    #html_content = response_lo.text
    #data = json.loads(response_lo.text)
    #print(">>>"+html_content)
    # 接口数据转换成json
    #data = json.loads(html_content)
    # 截取需要保存的数据
    #dataValue = data['datas']['table']['row']['table']['row']
    #print(data[1])

    # 数据库连接配置
    db_config = {
        'host': '191.254.8.220',
        'user': 'root',
        'password': 'Szmsa@520',
        'db': 'spiders',
        'charset': 'utf8mb4',
        'cursorclass': pymysql.cursors.DictCursor
    }
    # 表名和字段名
    table_name = 'xt_register_mortgage_ship'
    # 连接数据库
    connection = pymysql.connect(**db_config)

    try:
        # 开始事务
        with connection.cursor() as cursor:
            # 循环插入每条记录
            for record in strData:
                # 获取需要插入的字段名称
                fields = ', '.join(record.keys())
                # 拼装需要插入字段对应的%号个数
                values = ", ".join(['%s'] * len(record))
                # print("!!!"+fields)
                # print("~~~"+values)

                # value值是json的字段无法直接存入库，要转成string
                json_accept_date = record['accept_date']
                json_approval_date = record['approval_date']
                json_audit_date = record['audit_date']
                json_create_time = record['create_time']
                json_operate_time = record['operate_time']
                json_payoff_begin_date = record['payoff_begin_date']
                json_payoff_end_date = record['payoff_end_date']
                json_trial_date = record['trial_date']

                # json对象转成string后重新赋值回原来的字段
                str_accept_date = json.dumps(json_accept_date)
                str_approval_date = json.dumps(json_approval_date)
                str_audit_date = json.dumps(json_audit_date)
                str_create_time = json.dumps(json_create_time)
                str_operate_time = json.dumps(json_operate_time)
                str_payoff_begin_date = json.dumps(json_payoff_begin_date)
                str_payoff_end_date = json.dumps(json_payoff_end_date)
                str_trial_date = json.dumps(json_trial_date)

                record['accept_date'] = str_accept_date
                record['approval_date'] = str_approval_date
                record['audit_date'] = str_audit_date
                record['create_time'] = str_create_time
                record['operate_time'] = str_operate_time
                record['payoff_begin_date'] = str_payoff_begin_date
                record['payoff_end_date'] = str_payoff_end_date
                record['trial_date'] = str_trial_date

                names = record['accept_date']
                print(names)
                #print(tuple(record.keys()))
                # 构建插入语句
                insert_stmt = (
                    "INSERT INTO `{table}` ({fields}) "
                    "VALUES ({values})"
                ).format(
                    table=table_name,
                    fields=fields,
                    values=values
                )
                # 执行sql语句插入数据，record.values()用于获取字段中的值
                cursor.execute(insert_stmt, tuple(record.values()))
                # print("%%%" + insert_stmt)
                # print(">>>" + str(record.values()))
        # 提交事务
        connection.commit()
    finally:
        # 关闭数据库连接
        connection.close()